package com.supermap.dataservice.project.cq;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 统计重庆的决策服务产品
 * @date 2018年11月09 下午 3:55
 * @email 1301584033@qq.com
 */
@Mapper
public interface CqProductMapper {


    /***
     * 统计重庆决策产品总数
     */

    @SelectProvider(type = CqProductMapperSql.class ,method = "getCqProductCountNumSQL")
    List<Map<String,Object>> getCqProductCountNum(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("pageNum")String pageNum,@Param("pageSize")String pageSize);

    /**
     *根据区域ID获取决策服务产品
     * */
    @SelectProvider(type = CqProductMapperSql.class ,method = "getCqProductListByAreaCodeSQL")
    List<Map<String,Object>> getCqProductListByAreaCode(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("areaCode") String areaCode);

    /**
     *获取产品详情
     * */
    @SelectProvider(type = CqProductMapperSql.class ,method = "getCqProductListByAreaCodeDetailsSQL")
    List<Map<String,Object>> getCqProductListByAreaCodeDetails(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("areaCode") String areaCode,@Param("baseClassId") String baseClassId);


    class CqProductMapperSql{
        public String getCqProductCountNumSQL(Map<String,Object> map){
            String beginTime=(String) map.get("beginTime");
            String endTime=(String) map.get("endTime");
            String pageNum=(String)map.get("pageNum");
            String pageSize=(String) map.get("pageSize");
            StringBuilder stringBuilder= new StringBuilder();
            stringBuilder.append("select t1.ID as deparId,t1.departname,if(num is null,0,num) as num,t1.depareacode  from t_s_depart t1\n" +
                    "left join \n" +
                    "(select t3.org_id,count(t2.id) num  from t_b_pms_product t2\n" +
                    "inner join t_s_user_org t3 on t2.product_maker = t3.user_id and t2.product_state = 6");
            if(beginTime!=null&&endTime!=null){
                stringBuilder.append(" and t2.create_date between  '"+beginTime+"' and  '"+endTime+"' ");
            }
            stringBuilder.append(" group by t3.org_id) t4\n" +
                    "on t1.ID = t4.org_id ");

            if(pageNum!=null && pageSize!=null){
                stringBuilder.append("limit  "+pageNum+","+pageSize+" ");
            }

            return  stringBuilder.toString();
        }

        public String  getCqProductListByAreaCodeSQL(Map<String,Object> map){
            String beginTime=(String) map.get("beginTime");
            String endTime=(String) map.get("endTime");
            String areaCode=(String) map.get("areaCode");
            StringBuilder stringBuilder= new StringBuilder();
            stringBuilder.append("select t4.content_category  as classId,'"+areaCode+"' AS areaCode, t0.name,if(sum(t4.num) is null,0,sum(t4.num)) as num from  t_producttemplate t0 \n" +
                    "            left join \n" +
                    "            (select t2.group_name, t2.id,t2.content_category,t1.depareacode, count(t3.id) num from t_s_depart t1\n" +
                    "            inner join t_b_productgroup t2  on t1.org_code = t2.sys_org_code and t1.depareacode = "+areaCode+"\n" +
                    "            inner join t_b_pms_product t3 on t2.id = t3.product_group and t3.product_state = 6" );
                    if(beginTime!=null && endTime !=null){
                        stringBuilder.append("   and t3.create_date between '"+beginTime+"' and '"+endTime+"' " );
                    }
                    stringBuilder.append( "    group by t2.id) t4\n" +
                    "            on t0.id = t4.content_category\n" +
                    "            group by t0.id");
               return      stringBuilder.toString();

        }
        public String  getCqProductListByAreaCodeDetailsSQL(Map<String,Object> map){
            String beginTime=(String) map.get("beginTime");
            String endTime=(String) map.get("endTime");
            String areaCode=(String) map.get("areaCode");
            String baseClassId=(String) map.get("baseClassId");
            StringBuilder stringBuilder= new StringBuilder();
            stringBuilder.append("select t3.product_title,t2.group_name, date_format(t3.make_date,'%Y-%m-%d %H:%i:%S') as make_date, t3.id as productId, count(t3.id) num from t_s_depart t1\n" +
                    "            inner join t_b_productgroup t2  on t1.org_code = t2.sys_org_code and t1.depareacode = "+areaCode+"  and t2.content_category ="+baseClassId+" \n" +
                    "            inner join t_b_pms_product t3 on t2.id = t3.product_group and t3.product_state = 6");

            if(beginTime!=null && endTime !=null){
                stringBuilder.append("   and t3.create_date between '"+beginTime+"' and '"+endTime+"' " );
            }
            stringBuilder.append( "          group by t2.id");
            return      stringBuilder.toString();

        }


    }

}
